﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using QLTV_V2.DataAccess;
using QLTV_V2.BussinessObject;

namespace QLTV_V2.BussinessLogic
{
    class MuonTraBUS
    {
        ConnectData conn = new ConnectData();
        Utilities uti = new Utilities();
        DataTable tb = new DataTable();
        PhieuMuonTra phieuMT = new PhieuMuonTra();
        CTMuonTra ct = new CTMuonTra();
        DauSach ds = new DauSach();
        private string sql;
      
        #region Phieu muon tra
        public DataTable getPhieuMT()
        {
            sql = "select a.MaMuonTra, MaBanDoc, NgayMuon, NgayPhaiTra, count(MaCTMuonTra) as 'SoLuong' "+
                  "from PhieuMuonTra a join CTMuonTra b on a.MaMuonTra=b.MaMuonTra "+
                  "group by a.MaMuonTra, MaBanDoc, NgayMuon, NgayPhaiTra "+
                  "order by NgayMuon ";
            return conn.GetDataTable(sql);
        }
        public PhieuMuonTra getPhieuMT_object(string maMT)
        {
            sql = "select MaMuonTra, MaBanDoc, NgayMuon, NgayPhaiTra, MaNV from PhieuMuonTra " +
                  "where MaMuonTra=N'"+maMT+"'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            phieuMT.MaMuonTra = dr["MaMuonTra"].ToString();
            phieuMT.MaBanDoc = dr["MaBanDoc"].ToString();
            phieuMT.NgayMuon = Convert.ToDateTime(dr["NgayMuon"]);
            phieuMT.NgayPhaiTra = Convert.ToDateTime(dr["NgayPhaiTra"]);
            phieuMT.MaNV = dr["MaNV"].ToString();
            return phieuMT;
        }
        public bool insertPhieuMT(PhieuMuonTra MT)
        {
            int type = uti.GetTypeDate();
            sql = "insert into PhieuMuonTra values " +
                  "(N'" + MT.MaMuonTra + "',N'" + MT.MaBanDoc + "',convert(datetime,'" + MT.NgayMuon.ToShortDateString() + "'," + type + "),convert(datetime,'" + MT.NgayPhaiTra.ToShortDateString() + "'," + type + "),N'"+MT.MaNV+"')";
            if (conn.ExecuteQuery(sql) == true)
                return true;
            return false;
        }

        public bool checkExistMT(string maBanDoc)
        {
             //Lấy số phiếu
                sql = "select top 1 a.MaMuonTra " +
                    "from PhieuMuonTra a join CTMuonTra b on a.MaMUonTra=b.MaMuonTra " +
                    "where MaBanDoc=N'" + maBanDoc + "' and NgayTra is null " +
                    "order by NgayTra";
               int row=conn.GetDataTable(sql).Rows.Count;
                if (row == 0)
                    return false;
                else
                    return true;
        }

        public PhieuMuonTra getPhieuMT_Ob_TraSach(string maBanDoc)
        {
                //Lấy số phiếu
                sql = "select top 1 a.MaMuonTra " +
                    "from PhieuMuonTra a join CTMuonTra b on a.MaMUonTra=b.MaMuonTra " +
                    "where MaBanDoc=N'" + maBanDoc + "' and NgayTra is null " +
                    "order by NgayTra";
                DataRow dr1 = conn.GetDataTable(sql).Rows[0];
                string maMT = dr1["MaMuonTra"].ToString();

                //Lấy thông tin phiếu
                sql = "select MaMuonTra, MaBanDoc, NgayMuon, NgayPhaiTra, MaNV from PhieuMuonTra " +
                     "where MaMuonTra=N'" + maMT + "'";
                DataRow dr = conn.GetDataTable(sql).Rows[0];
                phieuMT.MaMuonTra = dr["MaMuonTra"].ToString();
                phieuMT.MaBanDoc = dr["MaBanDoc"].ToString();
                phieuMT.NgayMuon = Convert.ToDateTime(dr["NgayMuon"]);
                phieuMT.NgayPhaiTra = Convert.ToDateTime(dr["NgayPhaiTra"]);
                phieuMT.MaNV = dr["MaNV"].ToString();
                return phieuMT;

        }

        #endregion
     
        #region Chi tiet phieu muon tra
        public DataTable getCTMuonTra(string maMT)
        { 
            sql="select MaCTMuonTra, MaMuonTra, a.MaBanSach, TenDauSach,TinhTrang, NgayTra, NgayGiaHan "+
                "from CTMuonTra a join BanSach b on a.MaBanSach=b.MaBanSach "+
	            "join DauSach c on b.MaDauSach=c.MaDauSach "+
                "where MaMuonTra=N'"+maMT+"'";
            return conn.GetDataTable(sql);
        }
        public bool insertCTMuonTra(CTMuonTra ct)
        { 
            int type=uti.GetTypeDate();
            sql = "insert into CTMuonTra (MaCTMuonTra, MaMuonTra, MaBanSach) values "+
                  "(N'" + ct.MaCTMuonTra + "',N'" + ct.MaMuonTra + "',N'" + ct.MaBanSach + "')";
            if (conn.ExecuteQuery(sql) == true)
                return true;
            return false;
        }
        public bool update_TraSach(DateTime NgayTra, string maCT)
        {
            int type = uti.GetTypeDate();
            sql = "update CTMuonTra set NgayTra=convert(datetime,'" + NgayTra.ToShortDateString() + "'," + type + ") where MaCTMuonTra=N'" + maCT + "'";
            if (conn.ExecuteQuery(sql) == true)
                return true;
            return false;
        }
        public bool update_GiaHanMT(DateTime ngayGiahan, string maCT)
        {
            int type = uti.GetTypeDate();
            sql = "update CTMuonTra set NgayGiaHan=convert(datetime,'" + ngayGiahan.ToShortDateString() + "'," + type + ") where MaCTMuonTra=N'" + maCT + "'";
            if (conn.ExecuteQuery(sql) == true)
                return true;
            return false;
        }
        public bool update_BanSach_MT(string mabansach,string tinhtrang, int dk)
        {
            if(dk==1)//cho mươn
                sql = "update BanSach set TrangThai=N'Cho mượn' where MaBanSach=N'" + mabansach + "'";
            else //Tra sach
                sql = "update BanSach set TrangThai=N'Sẵn sàng', TinhTrang=N'"+tinhtrang+"' where MaBanSach=N'" + mabansach + "'";
            if (conn.ExecuteQuery(sql) == true)
                return true;
            return false;
        }
        public bool checkDKMuonTra(string mabandoc)
        {
            sql = "select * from PhieuMuonTra a join CTMuonTra b on a.MaMuonTra=b.MaMuonTra where MaBanDoc=N'" + mabandoc + "' and NgayTra is null";
            tb = conn.GetDataTable(sql);
            if (tb.Rows.Count > 0)
                return false;
            return true;
        }
        public bool checkTTThe(string mabandoc)
        {
            sql = "select * from BanDoc where MaBanDoc=N'"+mabandoc+"' and TTThe=N'Kích hoạt'";
            tb = conn.GetDataTable(sql);
            if (tb.Rows.Count == 0)
                return false;
            return true;
        }
        public int getSoluong(string mabandoc)
        {
            sql = "select SLSach from BanDoc a join NhomBanDoc b on a.MaNhom=b.MaNhom where MaBanDoc=N'" + mabandoc + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            int soluong = Convert.ToInt32(dr["SLSach"]);
            return soluong;
        }
        public int getSoNgayMuon(string mabandoc)
        {
            sql = "select TGMuon from BanDoc a join NhomBanDoc b on a.MaNhom=b.MaNhom where MaBanDoc=N'" + mabandoc + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            int soluong = Convert.ToInt32(dr["TGMuon"]);
            return soluong;
        }
        public DauSach getTTDauSach_object_MT(string mabansach)
        {
            sql = "select TenDauSach, TenTacGia, NamXB " +
                  "from BanSach a join DauSach b on a.MaDauSach=b.MaDauSach " +
                  "join TacGia c on b.MaTacGia=c.MaTacGia " +
                  "where MaBanSach=N'"+mabansach+"'";
            try
            {
                DataRow dr = conn.GetDataTable(sql).Rows[0];
                ds.TenDauSach = dr["TenDauSach"].ToString();
                ds.MaTacGia = dr["TenTacGia"].ToString();
                ds.NamXB = Convert.ToInt32(dr["NamXB"]);
            }
            catch { }
            return ds;
        }
        #endregion

        #region Thông báo hết hạn mượn sách
        DataTable getDSHetHan()
        {
            sql = "select distinct b.MaMuonTra,a.MaBanDoc, TenBanDoc,count(MaCTMuonTra)as SoLuongSach, NgayMuon, NgayPhaiTra,Email " +
                "from BanDoc a join PhieuMuonTra b on a.MaBanDoc=b.MaBanDoc " +
                "join CTMuonTra c on b.MaMuonTra=c.MaMuonTra " +
                "group by b.MaMuonTra,a.MaBanDoc, TenBanDoc,NgayMuon, NgayPhaiTra";
              
            return conn.GetDataTable(sql);
        }
        DataTable getCTMUonTra(string maMT)
        { 
            sql="select a.MaBanSach, TenDauSach, TenTacGia, GiaBia "+
                "from CTMuonTra a join BanSach b on a.MaBanSach=b.MaBanSach "+
				"join DauSach c on b.MaDauSach=c.MaDauSach "+
				"join TacGia d on c.MaTacGia=d.MaTacGia";
            return conn.GetDataTable(sql);
        }

        public bool checkExistMail()
        {
            sql = "select * from Email";
            if (conn.GetDataTable(sql).Rows.Count - 1 > 0)
                return true;
            return false;
        }
        public bool AddNewMail(Email mail)
        {
            sql = "insert into Email values (N'" + mail.nameMail + "',N'" + mail.passMail + "',N'" + mail.BCC + "',N'" + mail.Title + "',N'" + mail.Content + "')";
            if (conn.ExecuteQuery(sql) == true)
                return true;
            else
                return false;
        }
        public bool UpdateMail(Email mail)
        {
            sql = "update Email set nameMail=N'" + mail.nameMail + "', passMail=N'" + mail.passMail + "', bccName=N'" + mail.BCC + "',Title=N'" + mail.Title + "',Contents=N'" + mail.Content + "'";
            if (conn.ExecuteQuery(sql) == true)
                return true;
            return false;
        }
        #endregion
    }
}
